<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="cg20941">CREATE TRIGGER</title><body><p id="sql_command_desc">Defines a new trigger. User-defined triggers are not supported in Greenplum
Database.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">CREATE TRIGGER <varname>name</varname> {BEFORE | AFTER} {<varname>event</varname> [OR ...]}
       ON <varname>table</varname> [ FOR [EACH] {ROW | STATEMENT} ]
       EXECUTE PROCEDURE <varname>funcname</varname> ( <varname>arguments</varname> )</codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE TRIGGER</codeph> creates a new trigger. The trigger will
be associated with the specified table and will run the specified
function when certain events occur. </p><p>Due to the distributed nature of a Greenplum Database system, the
use of triggers is very limited in Greenplum Database. The function used
in the trigger must be <codeph>IMMUTABLE</codeph>, meaning it cannot
use information not directly present in its argument list. The function
specified in the trigger also cannot run any SQL or modify distributed
database objects in any way. Given that triggers are most often used
to alter tables (for example, update these other rows when this row is
updated), these limitations offer very little practical use of triggers
in Greenplum Database. For that reason, Greenplum does not support the
use of user-defined triggers in Greenplum Database. Triggers cannot be
used on append-optimized tables.</p><p>If multiple triggers of the same kind are defined for the same event,
they will be fired in alphabetical order by name. </p><p><codeph><xref href="SELECT.xml#topic1" type="topic" format="dita"/></codeph> does not modify any
        rows so you can not create <codeph>SELECT</codeph> triggers. Rules and views are more
        appropriate in such cases.</p></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name to give the new trigger. This must be distinct from the
name of any other trigger for the same table.</pd></plentry><plentry><pt>BEFORE  AFTER</pt><pd>Determines whether the function is called before or after the event.
If the trigger fires before the event, the trigger may skip the operation
for the current row, or change the row being inserted (for <codeph>INSERT</codeph>
and <codeph>UPDATE</codeph> operations only). If the trigger fires after
the event, all changes, including the last insertion, update, or deletion,
are visible to the trigger.</pd></plentry><plentry><pt><varname>event</varname></pt><pd>Specifies the event that will fire the trigger (<codeph>INSERT</codeph>,
            <codeph>UPDATE</codeph>, or <codeph>DELETE</codeph>). Multiple events can be specified
            using OR. </pd></plentry><plentry><pt><varname>table</varname></pt><pd>The name (optionally schema-qualified) of the table the trigger is
for. </pd></plentry><plentry><pt>FOR EACH ROW</pt><pt>FOR EACH STATEMENT</pt><pd>This specifies whether the trigger procedure should be fired once
for every row affected by the trigger event, or just once per SQL statement.
If neither is specified, <codeph>FOR EACH STATEMENT</codeph> is the default.
A trigger that is marked <codeph>FOR EACH ROW</codeph> is called once
for every row that the operation modifies. In contrast, a trigger that
is marked <codeph>FOR EACH STATEMENT</codeph> only runs once for
any given operation, regardless of how many rows it modifies.</pd></plentry><plentry><pt><varname>funcname</varname></pt><pd>A user-supplied function that is declared as <codeph>IMMUTABLE</codeph>,
taking no arguments, and returning type <codeph>trigger</codeph>, which
is run when the trigger fires. This function must not run SQL
or modify the database in any way. </pd></plentry><plentry><pt><varname>arguments</varname></pt><pd>An optional comma-separated list of arguments to be provided to the
function when the trigger is run. The arguments are literal string
constants. Simple names and numeric constants may be written here, too,
but they will all be converted to strings. Please check the description
of the implementation language of the trigger function about how the
trigger arguments are accessible within the function; it may be different
from normal function arguments.</pd></plentry></parml></section><section id="section5"><title>Notes</title><p>To create a trigger on a table, the user must have the <codeph>TRIGGER</codeph>
privilege on the table.</p></section><section id="section6"><title>Examples</title><p>Declare the trigger function and then a trigger:</p><codeblock>CREATE FUNCTION sendmail() RETURNS trigger AS 
'$GPHOME/lib/emailtrig.so' LANGUAGE C IMMUTABLE;

CREATE TRIGGER t_sendmail AFTER INSERT OR UPDATE OR DELETE 
ON mytable FOR EACH STATEMENT EXECUTE PROCEDURE sendmail();</codeblock></section><section id="section7"><title>Compatibility</title><p>The <codeph>CREATE TRIGGER</codeph> statement in Greenplum Database
implements a subset of the SQL standard. The following functionality
is currently missing: </p><ul><li id="cg152704">Greenplum Database has strict limitations on the function that is called
by a trigger, which makes the use of triggers very limited in Greenplum
Database. For this reason, triggers are not officially supported in Greenplum
Database.</li><li id="cg152900">SQL allows triggers to fire on updates to specific columns (e.g., <codeph>AFTER
UPDATE OF col1, col2</codeph>). </li><li id="cg152838">SQL allows you to define aliases for the 'old' and 'new' rows
or tables for use in the definition of the triggered action (e.g., <codeph>CREATE
TRIGGER ... ON tablename REFERENCING OLD ROW AS somename NEW ROW AS othername
...</codeph>). Since Greenplum Database allows trigger procedures to
be written in any number of user-defined languages, access to the data
is handled in a language-specific way.</li><li id="cg152852">Greenplum Database only allows the execution of a user-defined function
for the triggered action. The standard allows the execution of a number
of other SQL commands, such as <codeph>CREATE TABLE</codeph> as the triggered
action. This limitation is not hard to work around by creating a user-defined
function that runs the desired commands.</li><li id="cg152865">SQL specifies that multiple triggers should be fired in time-of-creation
order. Greenplum Database uses name order, which was judged to be more
convenient. </li><li id="cg153794">SQL specifies that <codeph>BEFORE DELETE</codeph> triggers on cascaded
deletes fire after the cascaded <codeph>DELETE</codeph> completes. The
Greenplum Database behavior is for <codeph>BEFORE DELETE</codeph> to
always fire before the delete action, even a cascading one. This is considered
more consistent.</li><li id="cg152893">The ability to specify multiple actions for a single trigger using <codeph>OR</codeph>
is a Greenplum Database extension of the SQL standard.</li></ul></section><section id="section8"><title>See Also</title><p><codeph><xref href="CREATE_FUNCTION.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="ALTER_TRIGGER.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="DROP_TRIGGER.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="CREATE_RULE.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
